﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;

/// <summary>
/// 生成sql脚本
/// </summary>
internal class CAutoSql
{
    /// <summary>
    /// 获取能自动生成代码的SQL
    /// </summary>
    /// <returns></returns>
    public static string GetAutoCreateCodeSql()
    {
        StringBuilder sb = new StringBuilder();

        #region 根据存储过程名生成 c# 代码

        sb.AppendLine("/*");
        sb.AppendLine("功能: 根据存储过程名生成 c# 代码");   
        sb.AppendLine("作者: vincent");                             
        sb.AppendLine("日期: " + DateTime.Now.ToString());                             
        sb.AppendLine("*/");
        sb.AppendLine("if object_id(N'[dbo].[cc]') is not null");
        sb.AppendLine("drop procedure [dbo].[cc]");
        sb.AppendLine("go");
        sb.AppendLine("CREATE procedure [dbo].[cc]");
        sb.AppendLine("@spname varchar(64),   -- 过程名称");
        sb.AppendLine("@exectype int = 0,           -- 0 void 方法, 1 list 方法， 2 detail 方法  3 datatable方法");
        sb.AppendLine("@havereturnval int = 0,      -- 是否有返回值, 0= 没有， 1= 有 ");
        sb.AppendLine("@tablename varchar(50) = ''  -- 可以写表名，如果不写将自动找到一个相关表");                             
        sb.AppendLine("as");
        sb.AppendLine("set nocount on");
   
        sb.AppendLine("if( object_id(@spname) is null )");
        sb.AppendLine("begin");  
        sb.AppendLine("    print 'not exists the procedure ' + @spname");  
        sb.AppendLine("    return");   
        sb.AppendLine("end");     
           
        sb.AppendLine("if( @tablename <> '' and object_id(@tablename) is null )");   
        sb.AppendLine("begin");   
        sb.AppendLine("    print 'not exists the table ' + @tablename");   
        sb.AppendLine("    return");   
        sb.AppendLine("end");     
                     
        sb.AppendLine("declare @tmptable table (code varchar(2048))");     
        sb.AppendLine("declare @tblist table(code varchar(2048))   -- 返回list时用");  
        sb.AppendLine("declare @tbdetail table(code varchar(2048))   -- 返回detail数据");  
        sb.AppendLine("declare @tbdatatable table(code varchar(2048))  -- 返回datatable时用");  
        sb.AppendLine("declare @tbvoid table(code varchar(2048))   -- 无返回值时用");  
        sb.AppendLine("declare @tbdeclare table(code varchar(2048))        -- 需要在代码中定义的变量");                        
        sb.AppendLine("declare @declares varchar(2048)                     -- 函数参数");                      
        sb.AppendLine("declare @adoclassname varchar(50)  -- xxAdo.cs 类名");  
        sb.AppendLine("declare @classname varchar(50)   -- xxInfo 类名");  
        sb.AppendLine("declare @pos int");    
        sb.AppendLine("declare @word varchar(5)");   
           
        sb.AppendLine("if( @tablename = '' )");   
        sb.AppendLine("    set @tablename = isnull( (select top 1 object_name(depid) from sysdepends where id = object_id(@spname) ), '' )");   
           
        sb.AppendLine("--print @tablename");    
           
        sb.AppendLine("set @classname = @tablename");    
        sb.AppendLine("if( @classname <> '' and left(@classname,2) = 't_')");   
        sb.AppendLine("begin");   
        sb.AppendLine("    set @classname = substring(@classname,3,len(@classname) - 2)");   
        sb.AppendLine("end");   
           
        sb.AppendLine("set @classname = upper(left(@classname,1)) + substring(@classname,2,len(@classname) - 1)");   
        sb.AppendLine("while( charindex('_',@classname) > 0 )");   
        sb.AppendLine("begin");   
        sb.AppendLine("    set @pos = charindex('_',@classname)");   
        sb.AppendLine("    set @word = substring(@classname,@pos + 1, 1)");   
        sb.AppendLine("    set @classname = replace(@classname,'_' + @word,upper(@word) )");   
        sb.AppendLine("end");   
           
        sb.AppendLine("set @adoclassname = upper(left(@classname,1)) + substring(@classname,2,len(@classname) - 1) + 'DAL.cs'");     
        sb.AppendLine("set @classname = upper(left(@classname,1)) + substring(@classname,2,len(@classname) - 1) + 'Info'");       
        sb.AppendLine("select @adoclassname as ado数据库操作类文件名");    
          
        sb.AppendLine("-- 存储过程参数总数量");                       
        sb.AppendLine("declare @totalcols int");                                                         
        sb.AppendLine("select @totalcols = count(colid) from syscolumns where id = object_id(@spname)");   
                                     
        sb.AppendLine("-- 生成变量定义代码");           
           
        sb.AppendLine("if( @totalcols > 0 )");   
        sb.AppendLine("    insert into @tbdeclare");                             
        sb.AppendLine("select");                              
        sb.AppendLine("case type_name(xusertype)");                             
        sb.AppendLine("when 'varchar' then  'string'");                             
        sb.AppendLine("when 'nvarchar' then  'string'");                             
        sb.AppendLine("when 'decimal' then 'decimal'");                             
        sb.AppendLine("when 'tinyint' then 'int'");                             
        sb.AppendLine("when 'smallint' then 'int'");                             
        sb.AppendLine("when 'datetime' then 'DateTime'");                             
        sb.AppendLine("when 'smalldatetime' then 'DateTime'");                             
        sb.AppendLine("when 'char' then 'string'");                             
        sb.AppendLine("when 'nchar' then 'string'");                             
        sb.AppendLine("when 'bigint'  then 'long'");                             
        sb.AppendLine("when 'text' then 'string'");                             
        sb.AppendLine("when 'ntext' then 'string'");        
        sb.AppendLine("when 'image' then 'byte[]'");   
        sb.AppendLine("when 'float' then 'float'");                      
        sb.AppendLine("when 'numeric' then 'float'"); 
        sb.AppendLine("else type_name(xusertype)");                             
        sb.AppendLine("end ");                             
        sb.AppendLine("+ ' '+  replace ( name ,'@','')");    
        sb.AppendLine("+ ' ' + case type_name(xusertype)");                             
        sb.AppendLine("when 'varchar' then  '= \"\"'");                             
        sb.AppendLine("when 'nvarchar' then  '= \"\"'");                             
        sb.AppendLine("when 'decimal' then '= 0'");                             
        sb.AppendLine("when 'tinyint' then '= 0'");                             
        sb.AppendLine("when 'smallint' then '= 0'");                             
        sb.AppendLine("when 'int' then '= 0'");                             
        sb.AppendLine("when 'datetime' then '= DateTime.Now'");                             
        sb.AppendLine("when 'smalldatetime' then '=DateTime.Now'");                             
        sb.AppendLine("when 'char' then '= \"\"'");                             
        sb.AppendLine("when 'nchar' then '= \"\"'");                             
        sb.AppendLine("when 'bigint'  then '= 0'");                             
        sb.AppendLine("when 'text' then '= \"\"'");                             
        sb.AppendLine("when 'ntext' then '= \"\"'");     
        sb.AppendLine("when 'image' then '=null'"); 
        sb.AppendLine("when 'float' then '0'");   
        sb.AppendLine("when 'numeric' then '0'");          
        sb.AppendLine("else '' end");   
        sb.AppendLine("+ ';'  from syscolumns  where id = object_id(@spname)  order by colid");                                                        
                                     
        sb.AppendLine("set @declares = ''");             
          
        sb.AppendLine("-- 循环获取输入参数");  
        sb.AppendLine("declare @colid int");  
        sb.AppendLine("declare @curpage cursor"); 
        sb.AppendLine("set @curpage = CURSOR read_only forward_only for select colid from syscolumns where id = object_id(@spname) and isoutparam=0 order by colid ");
        sb.AppendLine("open @curpage");  
        sb.AppendLine("fetch next from @curpage into @colid");  
        sb.AppendLine("while (@@FETCH_STATUS = 0)");  
        sb.AppendLine("begin");                                 
        sb.AppendLine(" select @declares = @declares + ");  
                                      
        sb.AppendLine(" case type_name(xusertype)");                             
        sb.AppendLine(" when 'varchar' then  'string'");                             
        sb.AppendLine(" when 'nvarchar' then 'string'");                             
        sb.AppendLine(" when 'decimal' then 'decimal'");                             
        sb.AppendLine(" when 'tinyint' then 'int'");                             
        sb.AppendLine(" when 'smallint' then 'int'");                             
        sb.AppendLine(" when 'datetime' then 'DateTime'");                             
        sb.AppendLine(" when 'smalldatetime' then 'DateTime'");                             
        sb.AppendLine(" when 'char' then 'string'");                             
        sb.AppendLine(" when 'nchar' then 'string'");                             
        sb.AppendLine(" when 'bigint'  then 'long'");                             
        sb.AppendLine(" when 'text' then 'string'");                             
        sb.AppendLine(" when 'ntext' then 'string'");     
        sb.AppendLine(" when 'image' then 'byte[]'");  
        sb.AppendLine(" when 'float' then 'float'");   
        sb.AppendLine(" when 'numeric' then 'float'");                           
        sb.AppendLine(" else type_name(xusertype)");                             
        sb.AppendLine(" end");                              
        sb.AppendLine("  +   ' '+  replace ( name ,'@','') + ', ' from syscolumns where id = object_id(@spname) and isoutparam=0 and colid = @colid order by colid");
        sb.AppendLine(" fetch next from @curpage into @colid");                            
        sb.AppendLine("end");  
        sb.AppendLine("close @curpage");  
           
        sb.AppendLine("set @curpage = CURSOR read_only forward_only for select colid from syscolumns  where id = object_id(@spname) and isoutparam=1 order by colid");   
        sb.AppendLine("open @curpage");  
        sb.AppendLine("fetch next from @curpage into @colid");  
        sb.AppendLine("while(@@FETCH_STATUS = 0)");  
        sb.AppendLine("begin");  
        sb.AppendLine(" select @declares = @declares +");                                                          
        sb.AppendLine(" case type_name(xusertype)");                             
        sb.AppendLine(" when 'varchar' then  'out string'");                             
        sb.AppendLine(" when 'nvarchar' then  'out string'");                             
        sb.AppendLine(" when 'decimal' then 'out decimal'");                             
        sb.AppendLine(" when 'tinyint' then 'out int'");                             
        sb.AppendLine(" when 'smallint' then 'out int'");                             
        sb.AppendLine(" when 'datetime' then 'out DateTime'");                             
        sb.AppendLine(" when 'smalldatetime' then 'DateTime'");                     
        sb.AppendLine(" when 'char' then 'out string'");                             
        sb.AppendLine(" when 'nchar' then 'out string'");                             
        sb.AppendLine(" when 'bigint'  then 'out long'");                      
        sb.AppendLine(" when 'text' then 'out string'");                             
        sb.AppendLine(" when 'ntext' then 'out string'");    
        sb.AppendLine(" when 'image' then 'out byte[]'");   
        sb.AppendLine(" when 'float' then 'out float'");   
        sb.AppendLine(" when 'numeric' then 'float'");                           
        sb.AppendLine(" else 'out ' + type_name(xusertype)");                             
        sb.AppendLine(" end");                              
        sb.AppendLine(" + ' ' +  replace ( name ,'@','') + ', '");   
        sb.AppendLine(" from syscolumns  where id = object_id(@spname) and isoutparam=1 and colid = @colid order by colid");   
        sb.AppendLine(" fetch next from @curpage into @colid");  
        sb.AppendLine("end "); 
        sb.AppendLine("close @curpage");  
        sb.AppendLine("deallocate @curpage");               
           
        sb.AppendLine("if( charindex('out int totalcount',@declares) > 0)");   
        sb.AppendLine("    set @declares = replace(@declares, 'out int', 'ref int')");   
        sb.AppendLine("if( charindex('out int total',@declares) > 0)");   
        sb.AppendLine("    set @declares = replace(@declares, 'out int', 'ref int') ");  
           
        sb.AppendLine("if ( @totalcols > 0 ) ");  
        sb.AppendLine("begin");   
           
        sb.AppendLine("    -- 生成void函数代码");   
        sb.AppendLine("    insert into @tbvoid select 'public static void ' + @spname + '(' +  left(@declares,len(@declares)-1) + ' , _Database db )'");   
           
        sb.AppendLine("    -- 生成list函数代码");   
        sb.AppendLine("    insert into @tblist select 'public static List<' + @classname + '> ' + @spname + '(' +  left(@declares,len(@declares)-1) +' , _Database db )'");                            
           
        sb.AppendLine("    -- 生成detail函数代码");   
        sb.AppendLine("    insert into @tbdetail select 'public static ' + @classname + ' ' + @spname + '(' +  left(@declares,len(@declares)-1) +' , _Database db )'");  
          
        sb.AppendLine("    -- 生成datatable函数代码");  
        sb.AppendLine("    insert into @tbdatatable select 'public static DataTable ' + @spname + '_table (' +  left(@declares,len(@declares)-1) +' , _Database db )'");                           
           
        sb.AppendLine("end ");  
        sb.AppendLine("else ");  
        sb.AppendLine("begin");   
           
        sb.AppendLine("    -- 生成void函数代码");   
        sb.AppendLine("    insert into @tbvoid select 'public static void ' + @spname + '( _Database db )'");   
           
        sb.AppendLine("    -- 生成list函数代码");   
        sb.AppendLine("    insert into @tblist select 'public static List<' + @classname + '> ' + @spname + '( _Database db )'");                            
           
        sb.AppendLine("    -- 生成detail函数代码");   
        sb.AppendLine("    insert into @tbdetail select 'public static ' + @classname + ' ' + @spname + '( _Database db )'");                            
               
        sb.AppendLine("    -- 生成datatable函数代码");  
        sb.AppendLine("    insert into @tbdatatable select 'public static DataTable ' + @spname + '_table ( _Database db )'");  
        sb.AppendLine("end");   
               
        sb.AppendLine("insert into @tbvoid");   
        sb.AppendLine("select '{' union all");   
        sb.AppendLine("select 'List<SqlParameter> parms = new List<SqlParameter>();' union all");   
        sb.AppendLine("select ''");    
           
        sb.AppendLine("insert into @tblist");   
        sb.AppendLine("select '{' union all");   
        sb.AppendLine("select 'List<SqlParameter> parms = new List<SqlParameter>();' union all");   
        sb.AppendLine("select ''");   
           
        sb.AppendLine("insert into @tbdetail");    
        sb.AppendLine("select '{' union all");   
        sb.AppendLine("select 'List<SqlParameter> parms = new List<SqlParameter>();' union all");   
        sb.AppendLine("select ''");    
          
        sb.AppendLine("insert into @tbdatatable");  
        sb.AppendLine("select '{' union all");  
        sb.AppendLine("select 'List<SqlParameter> parms = new List<SqlParameter>();' union all");   
        sb.AppendLine("select ''");  
           
        sb.AppendLine("declare @parmNameUpper varchar(50)");   
        sb.AppendLine("declare @parmName varchar(50)");   
        sb.AppendLine("declare @parmType varchar(50)");  
        sb.AppendLine("declare @isoutparam int");   
        sb.AppendLine("declare @length int");   
           
        sb.AppendLine("declare @cur cursor");    
        sb.AppendLine("set @cur = cursor read_only forward_only for");    
        sb.AppendLine("select name,type_name(xusertype),isoutparam,length from syscolumns where id = object_id(@spname) order by isoutparam   ");
           
        sb.AppendLine("open @cur");    
        sb.AppendLine("fetch next from @cur into @parmName,@parmType,@isoutparam,@length");   
        sb.AppendLine("while(@@fetch_status = 0)");   
        sb.AppendLine("begin ");  
           
        sb.AppendLine("    set @parmName = substring(@parmName,2,len(@parmName) - 1)");      
        sb.AppendLine("    set @parmNameUpper = upper(left(@parmName,1)) + substring(@parmName,2,len(@parmName) - 1)");   
           
        sb.AppendLine("    if( @isoutparam = 1)");   
        sb.AppendLine("    begin");   
           
        sb.AppendLine("        insert into @tmptable");    
        sb.AppendLine("    select '' union all");   
        sb.AppendLine("    select");   
        sb.AppendLine("    'SqlParameter p' + @parmNameUpper");   
        sb.AppendLine("    + ' = new SqlParameter(\"@' + @parmName + '\", SqlDbType.'");    
        sb.AppendLine("    + case @parmType");   
        sb.AppendLine("    when 'int' then 'Int, 4);'");                             
        sb.AppendLine("    when 'smallint' then 'SmallInt, 2)'");   
        sb.AppendLine("    when 'bigint' then 'BigInt, 8);'");                             
        sb.AppendLine("    when 'varchar' then 'VarChar, ' + convert(varchar,@length)  + ');'");    
        sb.AppendLine("    when 'nvarchar' then 'NVarChar, ' + convert(varchar,@length)  + ');'");   
        sb.AppendLine("    when 'tinyint' then 'TinyInt, 1);'");   
        sb.AppendLine("    when 'decimal' then 'Decimal);'");   
        sb.AppendLine("    when 'float' then 'Float);'");      
        sb.AppendLine("    when 'numeric' then 'float);'");                        
        sb.AppendLine("    when 'char' then 'Char, ' + convert(varchar,@length)  + ');'");   
        sb.AppendLine("    when 'nchar' then 'NChar,' + convert(varchar,@length)  + ');'");   
        sb.AppendLine("    when 'datetime' then 'DateTime);'");                             
        sb.AppendLine("    when 'smalldatetime' then 'SmallDateTime);'");   
        sb.AppendLine("    when 'text' then 'Text, ' + convert(varchar,@length)  + ');'");   
        sb.AppendLine("    when 'ntext' then 'NText, ' + convert(varchar,@length)  + ');'");   
        sb.AppendLine("    else @parmType end");   
           
        sb.AppendLine("        if( charindex('total',@parmName) > 0 )");   
        sb.AppendLine("            insert into @tmptable");    
        sb.AppendLine("            select 'p' + @parmNameUpper  + '.Direction = ParameterDirection.InputOutput;' union all");   
        sb.AppendLine("            select 'p' + @parmNameUpper  + '.Value = ' + @parmName + ';'");   
        sb.AppendLine("        else");   
        sb.AppendLine("            insert into @tmptable");    
        sb.AppendLine("            select 'p' + @parmNameUpper + '.Direction = ParameterDirection.Output;'");    
           
        sb.AppendLine("    insert into @tmptable");    
        sb.AppendLine("    select 'parms.Add(p' + @parmNameUpper + ');'");   
           
        sb.AppendLine("    end");   
        sb.AppendLine("    else");   
        sb.AppendLine("    begin");   
           
        sb.AppendLine("    insert into @tmptable");    
        sb.AppendLine("    select 'parms.Add(new SqlParameter(\"@'+ @parmName + '\",' + @parmName +  '));'");   
           
        sb.AppendLine("    end");   
           
        sb.AppendLine("    fetch next from @cur into @parmName,@parmType,@isoutparam,@length");   
           
        sb.AppendLine("end");   
           
        sb.AppendLine("if ( @havereturnval = 1 )");   
        sb.AppendLine("begin");   
           
        sb.AppendLine("    insert into @tmptable");    
        sb.AppendLine("    select 'SqlParameter pRetval = new SqlParameter(\"@retval\", SqlDbType.Int, 4);'");   
                   
        sb.AppendLine("    insert into @tmptable ");   
        sb.AppendLine("    select 'pRetval.Direction = ParameterDirection.ReturnValue;' ");   
           
        sb.AppendLine("    insert into @tmptable ");   
        sb.AppendLine("    select 'parms.Add(pRetval);'  union all");   
        sb.AppendLine("    select ''");   
           
        sb.AppendLine("end");   
           
        sb.AppendLine("insert into @tbvoid(code) select code from @tmptable");   
        sb.AppendLine("insert into @tblist(code) select code from @tmptable");   
        sb.AppendLine("insert into @tbdetail(code) select code from @tmptable");   
        sb.AppendLine("insert into @tbdatatable(code) select code from @tmptable");   
        sb.AppendLine("delete from @tmptable");   
           
        sb.AppendLine("if( @totalcols > 0 )");   
        sb.AppendLine("begin");     
        sb.AppendLine(" insert into @tbvoid select '' union all select 'db.execute_procedure(\"' + @spname + '\",parms);' union all select ''");   
        sb.AppendLine(" insert into @tblist select '' union all select 'db.fetch_procedure(\"' + @spname + '\",parms);' union all select ''");   
        sb.AppendLine(" insert into @tbdetail select '' union all select 'db.fetch_procedure(\"' + @spname + '\",parms);' union all select '' ");     
        sb.AppendLine(" insert into @tbdatatable select 'DataTable dt = null; ' union all select 'dt = db.GetDataTable(\"' + @spname + '\",parms);' union all select ''");  
        sb.AppendLine("end");   
        sb.AppendLine("else");   
        sb.AppendLine("begin");     
        sb.AppendLine(" insert into @tbvoid select '' union all select 'db.execute_procedure(\"' + @spname + '\");' union all select ''");   
        sb.AppendLine(" insert into @tblist select '' union all select 'db.fetch_procedure(\"' + @spname + '\");' union all select ''");   
        sb.AppendLine(" insert into @tbdetail select '' union all select 'db.fetch_procedure(\"' + @spname + '\");' union all select '' ");     
        sb.AppendLine(" insert into @tbdatatable select 'DataTable dt = null; ' union all select 'dt = db.GetDataTable(\"' + @spname + '\");' union all select ''");      
        sb.AppendLine("end");   
           
        sb.AppendLine("if exists(select 1 from syscolumns where id= object_id(@spname) and isoutparam = 1 )");   
        sb.AppendLine("begin ");  
           
        sb.AppendLine("insert into @tmptable select");                              
        sb.AppendLine(" replace(name,'@','') + '= ( p' +  upper(substring(name,2,1)) + substring(name,3,len(name) - 2)  + '.Value == System.DBNull.Value ) ? \"\" : Convert.ToString( p' + upper(substring(name,2,1)) + substring(name,3,len(name) - 2)  + '.Value ) ; '");               
         
                      
        sb.AppendLine("from syscolumns  where id = object_id(@spname) and isoutparam=1");                              
        sb.AppendLine(" and ( type_name(xusertype)='varchar' or type_name(xusertype)='char' or type_name(xusertype)='text' or type_name(xusertype)='nchar' or type_name(xusertype)='nvarchar' or type_name(xusertype)='ntext' )");                             
        sb.AppendLine(" order by colid");                              
                                  
        sb.AppendLine("insert into @tmptable select");                              
        sb.AppendLine(" replace(name,'@','') + '= ( p'  + upper(substring(name,2,1)) + substring(name,3,len(name) - 2)  +  '.Value == System.DBNull.Value ) ? 0 : Convert.ToInt32( p' + upper(substring(name,2,1)) + substring(name,3,len(name) - 2) + '.Value ) ; '");                  
         
                   
        sb.AppendLine("from syscolumns  where id = object_id(@spname) and isoutparam=1");                              
        sb.AppendLine(" and ( type_name(xusertype)='int' or type_name(xusertype)='bigint' or type_name(xusertype)='tinyint' or type_name(xusertype)='smallint' )");                             
        sb.AppendLine(" order by colid");                              
           
        sb.AppendLine("insert into @tmptable select ");                             
        sb.AppendLine(" replace(name,'@','') + '= ( p'  + upper(substring(name,2,1)) + substring(name,3,len(name) - 2)  +  '.Value == System.DBNull.Value ) ? 0 : Convert.ToInt64( p' + upper(substring(name,2,1)) + substring(name,3,len(name) - 2) + '.Value ) ; '");                  
         
                   
        sb.AppendLine("from syscolumns  where id = object_id(@spname) and isoutparam=1 and type_name(xusertype)='bigint'");   
        sb.AppendLine(" order by colid");                              
           
        sb.AppendLine("insert into @tmptable select ''");   
           
        sb.AppendLine("end");                             
           
        sb.AppendLine("if ( @havereturnval=1 )");                             
        sb.AppendLine("begin");                     
        sb.AppendLine("    insert into @tmptable select 'int retval = ( pRetval.Value == System.DBNull.Value ) ? 0 : Convert.ToInt32(pRetval.Value); ' union all");   
        sb.AppendLine("    select ''");   
        sb.AppendLine("end");                         
           
        sb.AppendLine("insert into @tbvoid(code) select code from @tmptable");   
        sb.AppendLine("insert into @tblist(code) select code from @tmptable");   
        sb.AppendLine("insert into @tbdetail(code) select code from @tmptable");   
        sb.AppendLine("insert into @tbdatatable(code) select code from @tmptable");   
        sb.AppendLine("delete from @tmptable");   
           
        sb.AppendLine("if( @tablename <> '')");    
        sb.AppendLine("begin");    
        sb.AppendLine("    --取列表");    
        sb.AppendLine("    insert into @tblist");    
        sb.AppendLine("    select 'List<' + @classname +  '> list = new List<' + @classname +  '>() ;' union all");        
        sb.AppendLine("    select 'for (int i = 0;i < db.num_rows ;i++ ) ' union all");        
        sb.AppendLine("    select '{ ' union all");        
        sb.AppendLine("    select @classname +  ' c = new ' + @classname + '(); '  union all");        
        sb.AppendLine("    select");         
        sb.AppendLine("    'c.' + upper(left(a.name,1)) + right(a.name,len(a.name) - 1) + ' = (db.rows[i][\"' + a.name + '\"] == System.DBNull.Value) ? ' +");         
        sb.AppendLine("    case b.name");    
        sb.AppendLine("    when 'decimal' then '0 : Convert.ToDecimal(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'int' then '0 : Convert.ToInt32(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'smallint' then '0 : Convert.ToInt32(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'tinyint' then '0 : Convert.ToInt32(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'bigint' then '0 : Convert.ToInt64(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'bit' then '0 : Convert.ToInt32(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'char' then '\"\" : db.rows[i][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nchar' then '\"\" : db.rows[i][\"' + a.name + '\"].ToString()'");        
          
        sb.AppendLine("    when 'varchar' then '\"\" : db.rows[i][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nvarchar' then '\"\" : db.rows[i][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nvarchar' then '\"\" : db.rows[i][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'smalldatetime' then 'DateTime.MinValue : Convert.ToDateTime(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'datetime' then 'DateTime.MinValue : Convert.ToDateTime(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'float' then '0 : Convert.ToSingle(db.rows[i][\"' + a.name + '\"].ToString())'");    
        sb.AppendLine("    when 'numeric' then '0 : Convert.ToSingle(db.rows[i][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    else '\"\" : db.rows[i][\"' + a.name + '\"].ToString()' end + ';'");        
        sb.AppendLine("    from syscolumns a inner join systypes b");        
        sb.AppendLine("    on a.xtype = b.xtype");         
        sb.AppendLine("    where a.id = object_id(@tablename) union all");        
        sb.AppendLine("    select 'list.Add(c); ' union all");     
        sb.AppendLine("    select '} ' union all");        
        sb.AppendLine("    select 'return list; '");         
               
        sb.AppendLine("    --取详细");    
        sb.AppendLine("    insert into @tbdetail");    
        sb.AppendLine("    select 'if(db.num_rows == 0) ' union all");        
        sb.AppendLine("    select '{' union all");        
        sb.AppendLine("    select 'return null;' union all");        
        sb.AppendLine("    select '}' union all");        
        sb.AppendLine("    select 'else' union all");        
        sb.AppendLine("    select '{' union all");        
        sb.AppendLine("    select @classname + ' c = new ' + @classname + '(); ' union all");        
        sb.AppendLine("    select");         
        sb.AppendLine("    'c.' + upper(left(a.name,1)) + right(a.name,len(a.name) - 1) + ' = (db.rows[0][\"' + a.name + '\"] == System.DBNull.Value)?' +");         
        sb.AppendLine("    case b.name");         
        sb.AppendLine("    when 'decimal' then '0 : Convert.ToDecimal(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'int' then '0 : Convert.ToInt32(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'smallint' then '0 : Convert.ToInt32(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'tinyint' then '0 : Convert.ToInt32(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'bigint' then '0 : Convert.ToInt64(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'bit' then '0 : Convert.ToInt32(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'char' then '\"\" : db.rows[0][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nchar' then '\"\" : db.rows[0][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'varchar' then '\"\" : db.rows[0][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nvarchar' then '\"\" : db.rows[0][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'nvarchar' then '\"\" : db.rows[0][\"' + a.name + '\"].ToString()'");        
        sb.AppendLine("    when 'smalldatetime' then 'DateTime.MinValue : Convert.ToDateTime(db.rows[0][\"' + a.name + '\"].ToString())'");        
        sb.AppendLine("    when 'datetime' then 'DateTime.MinValue : Convert.ToDateTime(db.rows[0][\"' + a.name + '\"].ToString())'");  
        sb.AppendLine("    when 'float' then '0 : Convert.ToSingle(db.rows[0][\"' + a.name + '\"].ToString())'");          
        sb.AppendLine("    when 'numeric' then '0 : Convert.ToSingle(db.rows[0][\"' + a.name + '\"].ToString())'");    
        sb.AppendLine("    else '\"\" : db.rows[0][\"' + a.name + '\"].ToString()' end + ';'");        
        sb.AppendLine("    from syscolumns a inner join systypes b  ");      
        sb.AppendLine("    on a.xtype = b.xtype");         
        sb.AppendLine("    where a.id = object_id(@tablename) union all ");       
        sb.AppendLine("    select 'return c;' union all");        
        sb.AppendLine("    select '}'");      
          
        sb.AppendLine("    -- 取datatable ");  
        sb.AppendLine("    insert into @tbdatatable");    
        sb.AppendLine("    select 'return dt; '");      
        sb.AppendLine("end ");   
                                  
        sb.AppendLine("insert into @tbvoid select '}'");    
        sb.AppendLine("insert into @tblist select '}' ");   
        sb.AppendLine("insert into @tbdetail select '}' ");   
        sb.AppendLine("insert into @tbdatatable select '}'");    
             
        sb.AppendLine("select 'using System.Data.SqlClient;' as using_namespace union all");    
        sb.AppendLine("select 'using System.Collections.Generic;' as using_namespace");    
           
        sb.AppendLine("if( @exectype = 0)");    
        sb.AppendLine("    select code as void方法 from @tbvoid ");   
        sb.AppendLine("else if( @exectype = 1 and @tablename <> '') ");   
        sb.AppendLine("    select code as list返回列表方法 from @tblist "); 
        sb.AppendLine("else if( @exectype = 2 and @tablename <> '') ");   
        sb.AppendLine("    select code as detail返回详细方法 from @tbdetail");    
        sb.AppendLine("else if( @exectype = 3 and @tablename <> '') ");  
        sb.AppendLine("    select code as datatable返回Datatable from @tbdatatable");              
        sb.AppendLine("select code as 定义变量 from @tbdeclare"); 
        sb.AppendLine("GO\r");

        #endregion
        

        #region 根据表名生成常用存储过程

        sb.AppendLine("/*");         
        sb.AppendLine("功能: 根据表名生成常用存储过程");   
        sb.AppendLine("作者: vincent");                             
        sb.AppendLine("日期: " + DateTime.Now.ToString());                                                                 
        sb.AppendLine("*/");

        sb.AppendLine("if object_id(N'[dbo].[cp]') is not null");
        sb.AppendLine("drop procedure [dbo].[cp]");
        sb.AppendLine("go");
        sb.AppendLine("CREATE procedure [dbo].[cp]");  
        sb.AppendLine("@tablename varchar(50) = '' -- 表名称");     
        sb.AppendLine("as");     
        sb.AppendLine("set nocount on		-- 不返回计数，提高执行效率");
        sb.AppendLine("set transaction isolation level read uncommitted	-- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；");
		sb.AppendLine("										            -- 其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。");
        sb.AppendLine("set xact_abort on	-- 语句产生运行时错误，整个事务将终止并回滚");
          
        sb.AppendLine("declare @tableid int ");      
        sb.AppendLine("set @tableid = object_id(@tablename)");  
          
        sb.AppendLine("if @tableid is null ");  
        sb.AppendLine("begin");
        sb.AppendLine("    select 'table not exists' as error "); 
        sb.AppendLine("    return");      
        sb.AppendLine("end "); 
          
        sb.AppendLine("declare @tmptb table(code varchar(8000))");   
          
        sb.AppendLine("declare @fieldsName varchar(8000)");   
        sb.AppendLine("declare @parmName varchar(8000) ");  
        sb.AppendLine("declare @keyname varchar(50) ");  
        sb.AppendLine("declare @proctablename varchar(50)");   
          
        sb.AppendLine("declare @ident_seed int");   
        sb.AppendLine("select @ident_seed = ident_seed(@tablename)");   
        sb.AppendLine("if(@ident_seed is null)");   
        sb.AppendLine("    set @ident_seed = -1 ");  
          
        sb.AppendLine("set @proctablename = @tablename ");   
        sb.AppendLine("if( left(@proctablename,2) ='t_')");   
        sb.AppendLine("    set @proctablename = substring(@proctablename,3,len(@proctablename) - 2)");   
          
        sb.AppendLine("print @proctablename");   
          
        sb.AppendLine("select top 1 @keyname= [name] from syscolumns where id = @tableid order by colid ");   
          
        sb.AppendLine("set @fieldsname = ''");   
        sb.AppendLine("select @fieldsname = @fieldsname + a.[name] + ','");    
        sb.AppendLine("from syscolumns a inner join systypes b on a.xtype = b.xtype ");  
        sb.AppendLine("where id = @tableid and a.colid <> @ident_seed");   
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime'");   
        sb.AppendLine("order by colid");   
          
        sb.AppendLine("if( right(@fieldsname,1) = ',' )");  
        sb.AppendLine("    set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)");  
          
        sb.AppendLine("set @parmname = ''");  
        sb.AppendLine("select @parmname = @parmname + '@' + a.[name] + ','");   
        sb.AppendLine("from syscolumns a inner join systypes b on a.xtype = b.xtype");  
        sb.AppendLine("where id = @tableid and a.colid <> @ident_seed "); 
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime' "); 
        sb.AppendLine("order by colid "); 
          
        sb.AppendLine("if( right(@parmname,1) = ',' )");  
        sb.AppendLine("    set @parmname = substring(@parmname,1,len(@parmname) - 1) "); 
          
        sb.AppendLine("-- proc_pagelist1");  
        sb.AppendLine("delete from @tmptb "); 
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_list'') is not null' "); 
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_list' "); 
        sb.AppendLine("insert into @tmptb select 'go' "); 
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");  
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_list ,1,0'");  
        sb.AppendLine("insert into @tmptb select '-- Author: vincent' "); 
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: '");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' "); 
        sb.AppendLine("insert into @tmptb select ''");  
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_list'");  
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select '@page int = 1, --当前页' union all "); 
        sb.AppendLine("select '@pagesize int = 10, --每页显示' union all  ");
        sb.AppendLine("select '@total int = 0 output, --总记录数' union all  ");
        sb.AppendLine("select '@typeid int = 0 ''这里需要修改''' union all  ");
        sb.AppendLine("select 'as' union all  ");
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all  ");
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all "); 
        sb.AppendLine("select ''");  

        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select 'if( @total = 0 )' union all");  
        sb.AppendLine("select '    select @total = count(1) from ' + @tablename + ' where typeid = @typeid ''这里需要修改''' union all"); 
        sb.AppendLine("select '' union all "); 
        sb.AppendLine("select 'if( @page = 1 )' union all  ");
        sb.AppendLine("select 'begin' union all  ");
        sb.AppendLine("select '    set rowcount @pagesize' union all "); 
        sb.AppendLine("select '    select * from ' + @tablename + ' where typeid = @typeid ''这里需要修改'' order by ' + @keyname +  ' desc ' union all  ");
        sb.AppendLine("select '    set rowcount 0' union all");  
        sb.AppendLine("select '    return' union all "); 
        sb.AppendLine("select 'end' union all "); 
        sb.AppendLine("select 'else' union all  ");
        sb.AppendLine("select 'begin' union all  ");
        sb.AppendLine("select '    declare @tb table(rownum int identity(1,1),keyid int)' union all");  
        sb.AppendLine("select '    insert into @tb(keyid) select ' + @keyname + ' from ' + @tablename union all  ");
        sb.AppendLine("select '        where typeid = @typeid ''这里需要修改'' order by ' + @keyname +  ' desc' union all  ");
        sb.AppendLine("select '    select a.* from ' + @tablename + ' a inner join @tb b on a.'+ @keyname +  ' = b.keyid ' union all  ");
        sb.AppendLine("select '        where b.rownum > @pagesize * (@page - 1) and b.rownum <= @pagesize * @page' union all  ");
        sb.AppendLine("select 'end' "); 
          
        sb.AppendLine("insert into @tmptb select '' union all select 'go' "); 
          
        sb.AppendLine("select code as proc_pagelist1 from @tmptb  ");
          
        sb.AppendLine("-- proc_pagelist2");   
        sb.AppendLine("delete from @tmptb "); 
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_list'') is not null'");  
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_list' "); 
        sb.AppendLine("insert into @tmptb select 'go' "); 
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");  
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_list ,1,0'");  
        sb.AppendLine("insert into @tmptb select '-- Author: vincent' "); 
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: '");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");  
        sb.AppendLine("insert into @tmptb select '' "); 
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_list'");  
          
        sb.AppendLine("insert into @tmptb ");   
        sb.AppendLine("select '@page int = 1, --当前页' union all");   
        sb.AppendLine("select '@pagesize int = 10, --每页显示' union all ");  
        sb.AppendLine("select '@total int = 0 output, --总记录数' union all ");  
        sb.AppendLine("select '@condition varchar(200) = '''' ' union all ");  
        sb.AppendLine("select 'as' union all");   
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all ");  
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  "); 
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all  "); 
        sb.AppendLine("select '' ");  
          
        sb.AppendLine("insert into @tmptb ");   
        sb.AppendLine("select 'declare @tablename varchar(1024)' union all");              
        sb.AppendLine("select 'declare @orderby  varchar(1024)' union all ");        
        sb.AppendLine("select 'declare @orderby2 varchar(1024)' union all ");        
        sb.AppendLine("select 'declare @orderbyclause varchar(1024)' union all ");        
        sb.AppendLine("select 'declare @keyname varchar(50)' union all ");        
        sb.AppendLine("select 'declare @keyinorder tinyint' union all ");  
        sb.AppendLine("select 'declare @whereclause varchar(1024)' union all");         
        sb.AppendLine("select 'declare @showclause varchar(1024)' union all");       
        sb.AppendLine("select '' union all");           
        sb.AppendLine("select '-- 表名称' union all");   
        sb.AppendLine("select 'set @tablename = ''' + @tablename + ''' ' union all ");             
        sb.AppendLine("select '--排序字段,字段前面加b.,字段后加 desc或asc,格式如 b.rid desc,b.rname asc' union all");  
        sb.AppendLine("select 'set @orderby = ''b.' + @keyname + ' desc'' ' union all ");             
        sb.AppendLine("select '--排序字段,字段前面加b.,字段后面和上面的orderby正好相反,格式如 b.rid asc,b.ranme desc' union all ");  
        sb.AppendLine("select 'set @orderby2 = ''b.' + @keyname + ' asc'' '  union all ");  
        sb.AppendLine("select '--排序字段，这里不需要加 asc 和 desc，格式如 b.rid,b.rname' union all ");  
        sb.AppendLine("select 'set @orderbyclause = ''b.' + @keyname + '''' union all"); 
        sb.AppendLine("select '--主键是否在排序字段里，1 是 0 不是，一般是1' union all "); 
        sb.AppendLine("select 'set @keyinorder = 1'  union all");              
        sb.AppendLine("select '--这个表的主键' union all");   
        sb.AppendLine("select 'set @keyname = ''' + @keyname +  ''''  union all");              
        sb.AppendLine("select '--查询条件' union all ");  
        sb.AppendLine("select 'set @whereclause = @condition'  union all");              
        sb.AppendLine("select 'set @showclause = ''a.*'''   union all");               
        sb.AppendLine("select '' union all ");  
        sb.AppendLine("select '--调用分页存储过程' union all ");  
        sb.AppendLine("select 'exec P_SplitPageOneSql_v2 @tablename,@pagesize,@page,@orderby,@orderby2,' union all ");             
        sb.AppendLine("select '@orderbyclause,@keyname,@keyinorder,@whereclause,@showclause,@total output'");   
          
        sb.AppendLine("insert into @tmptb select '' union all select 'go'");   
          
        sb.AppendLine("select code as proc_pagelist2 from @tmptb ");  
          
        sb.AppendLine("-- proc_insert ");  
        sb.AppendLine("delete from @tmptb ");  
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_insert'') is not null'");   
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_insert'");   
        sb.AppendLine("insert into @tmptb select 'go' ");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' ");  
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_insert,0,1'");   
        sb.AppendLine("insert into @tmptb select '-- Author: vincent' ");  
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  "); 
        sb.AppendLine("insert into @tmptb select '-- Description: ' ");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");   
        sb.AppendLine("insert into @tmptb select ''");   
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_insert' ");  
          
        sb.AppendLine("insert into @tmptb ");   
        sb.AppendLine("select   "); 
        sb.AppendLine("'@' + a.name + ' ' +   "); 
        sb.AppendLine("case b.name");   
        sb.AppendLine("when 'char' then 'char(' + convert(varchar,a.length) + ') = '''',' ");     
        sb.AppendLine("when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','  ");    
        sb.AppendLine("when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','  ");    
        sb.AppendLine("when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''',' "); 
        sb.AppendLine("else b.name + ' = 0,' end  ");
        sb.AppendLine("from syscolumns a inner join systypes b "); 
        sb.AppendLine("on a.xtype = b.xtype");       
        sb.AppendLine("where a.id = object_id(@tablename) ");  
        sb.AppendLine("and a.colid <> @ident_seed  ");
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime'  ");
        sb.AppendLine("order by colid ");    
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select '@outmsg varchar(256) = '''' output,' union all  ");
        sb.AppendLine("select '@newid int = 0 output' union all "); 
        sb.AppendLine("select 'as' union all "); 
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all");  
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all");   
        sb.AppendLine("select ''");  
          
        sb.AppendLine("insert into @tmptb");   
        sb.AppendLine("select 'insert into ' + @tablename + '(' + @fieldsname + ')' union all "); 
        sb.AppendLine("select '    values(' + @parmname + ')' "); 
          
        sb.AppendLine("insert into @tmptb  "); 
        sb.AppendLine("select ' ' union all ");  
        sb.AppendLine("select 'if @@rowcount = 0 ' union all ");  
        sb.AppendLine("select 'begin' union all ");  
        sb.AppendLine("select '    set @outmsg = ''添加失败''' union all ");  
        sb.AppendLine("select '    return 0' union all ");  
        sb.AppendLine("select 'end' union all  "); 
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select 'set @newid = @@identity' union all");   
        sb.AppendLine("select '' union all ");  
        sb.AppendLine("select 'if @newid <= 0' union all ");  
        sb.AppendLine("select 'begin' union all ");  
        sb.AppendLine("select '    set @outmsg = ''添加失败''' union all");   
        sb.AppendLine("select '    return 0' union all ");  
        sb.AppendLine("select 'end' union all ");  
        sb.AppendLine("select '' union all  "); 
        sb.AppendLine("select 'set @outmsg = ''添加成功''' union all ");  
        sb.AppendLine("select 'return 1' union all ");  
        sb.AppendLine("select '' union all ");  
        sb.AppendLine("select 'go' "); 
          
        sb.AppendLine("select code as proc_insert from @tmptb "); 
          
          
        sb.AppendLine("-- proc_save 更新 与 插入 "); 
        sb.AppendLine("delete from @tmptb "); 
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_save'') is not null'");  
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_save'");  
        sb.AppendLine("insert into @tmptb select 'go'");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");  
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_save,0,1'");  
        sb.AppendLine("insert into @tmptb select '-- Author: vincent'");  
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: 数据保存--更新与插入'  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' "); 
        sb.AppendLine("insert into @tmptb select ''");  
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_save'");  
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select ");  
        sb.AppendLine("'@' + a.name + ' ' + ");  
        sb.AppendLine("case b.name ");
        sb.AppendLine("when 'char' then 'char(' + convert(varchar,a.length) + ') = '''',' ");     
        sb.AppendLine("when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''',' ");     
        sb.AppendLine("when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','");      
        sb.AppendLine("when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','");  
        sb.AppendLine("when 'text' then 'text='''',' "); 
        sb.AppendLine("else b.name + ' = 0,' end  ");
        sb.AppendLine("from syscolumns a inner join systypes b ");     
        sb.AppendLine("on a.xtype = b.xtype       ");
        sb.AppendLine("where a.id = object_id(@tablename) and  "); 
        sb.AppendLine("b.name <> 'datetime' and b.name <> 'smalldatetime'  ");
        sb.AppendLine("order by colid     ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '@outmsg varchar(256) = '''' output,' union all  ");
        sb.AppendLine("select '@newid int = 0 output' union all  ");
        sb.AppendLine("select 'as' union all  ");
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all "); 
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all "); 
        sb.AppendLine("select '' "); 
          
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select 'if @' + @keyname + ' <= 0 ' union all  ");
        sb.AppendLine("select 'begin' union all");  
        sb.AppendLine("select '   insert into ' + @tablename + '(' + @fieldsname + ')' union all  ");
        sb.AppendLine("select '    values(' + @parmname + ')'  ");
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select ' ' union all  "); 
        sb.AppendLine("select '   if @@rowcount <= 0 ' union all ");  
        sb.AppendLine("select '   begin' union all  "); 
        sb.AppendLine("select '      set @outmsg = ''添加失败''' union all  "); 
        sb.AppendLine("select '      return 0' union all ");  
        sb.AppendLine("select '   end' union all   ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select '  set @newid = @@identity' union all "); 
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select '  if @newid <= 0' union all   ");
        sb.AppendLine("select '  begin' union all  ");
        sb.AppendLine("select '     set @outmsg = ''添加失败''' union all ");  
        sb.AppendLine("select '     return 0' union all  "); 
        sb.AppendLine("select '  end' union all   ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select '  set @outmsg = ''添加成功''' union all");   
        sb.AppendLine("select '  return 1' union all ");  
        sb.AppendLine("select '' union all "); 
        sb.AppendLine("select 'end' union all   ");
        sb.AppendLine("select 'else' union all   ");
        sb.AppendLine("select 'begin'  "); 
          
        sb.AppendLine("set @fieldsname = ''  ");
        sb.AppendLine("select @fieldsname = @fieldsname + a.[name] + ' = ' + '@' + a.[name] + ',' "); 
        sb.AppendLine("from syscolumns a inner join systypes b on a.xtype = b.xtype "); 
        sb.AppendLine("where id = @tableid and a.colid <> @ident_seed  ");
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime'  ");
        sb.AppendLine("order by colid  ");
          
        sb.AppendLine("if( right(@fieldsname,1) = ',' ) "); 
        sb.AppendLine("    set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1) "); 
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '   update ' + @tablename union all "); 
        sb.AppendLine("select '    set ' + @fieldsname  union all  ");
        sb.AppendLine("select '    where ' + @keyname + ' = @' + @keyname");  
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select '   if @@rowcount <= 0 ' union all ");  
        sb.AppendLine("select '   begin' union all  ");
        sb.AppendLine("select '      set @outmsg = ''更新失败''' union all ");  
        sb.AppendLine("select '      return 0' union all ");  
        sb.AppendLine("select '   end' union all   ");
        sb.AppendLine("select ''   ");
          
        sb.AppendLine("insert into @tmptb  "); 
        sb.AppendLine("select '   set @newid = @' + @keyname union all");   
        sb.AppendLine("select '   set @outmsg = ''更新成功''' union all ");  
        sb.AppendLine("select '   return 1' union all");  
        sb.AppendLine("select '' union all  "); 
        sb.AppendLine("select 'end' union all  "); 
        sb.AppendLine("select '' union all ");  
        sb.AppendLine("select 'go' "); 
          
        sb.AppendLine("select code as proc_save from @tmptb");  
          
        sb.AppendLine("-- proc_update "); 
        sb.AppendLine("delete from @tmptb  ");
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_update'') is not null' "); 
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_update'"); 
        sb.AppendLine("insert into @tmptb select 'go'  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' "); 
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_update,0,1'  ");
        sb.AppendLine("insert into @tmptb select '-- Author: vincent'  ");
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: '  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'  ");
        sb.AppendLine("insert into @tmptb select ''  ");
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_update' "); 
          
        sb.AppendLine("insert into @tmptb  "); 
        sb.AppendLine("select   ");
        sb.AppendLine("'@' + a.name + ' ' +  "); 
        sb.AppendLine("case b.name   ");
        sb.AppendLine("when 'char' then 'char(' + convert(varchar,a.length) + ') = '''','   ");   
        sb.AppendLine("when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','      ");
        sb.AppendLine("when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','    ");  
        sb.AppendLine("when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','  ");
        sb.AppendLine("else b.name + ' = 0,' end  ");
        sb.AppendLine("from syscolumns a inner join systypes b      ");
        sb.AppendLine("on a.xtype = b.xtype       ");
        sb.AppendLine("where a.id = object_id(@tablename)  ");    
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime'  ");
        sb.AppendLine("order by colid    ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '@outmsg varchar(256) = '''' output' union all  ");
        sb.AppendLine("select 'as' union all  ");
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all "); 
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all   ");
        sb.AppendLine("select ''");  
          
        sb.AppendLine("set @fieldsname = ''  ");
        sb.AppendLine("select @fieldsname = @fieldsname + a.[name] + ' = ' + '@' + a.[name] + ',' ");  
        sb.AppendLine("from syscolumns a inner join systypes b on a.xtype = b.xtype "); 
        sb.AppendLine("where id = @tableid and a.colid <> @ident_seed  ");
        sb.AppendLine("and b.name <> 'datetime' and b.name <> 'smalldatetime'  ");
        sb.AppendLine("order by colid  ");
          
        sb.AppendLine("if( right(@fieldsname,1) = ',' )  ");
        sb.AppendLine("    set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)  ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select 'update ' + @tablename union all  ");
        sb.AppendLine("select ' set ' + @fieldsname  union all  ");
        sb.AppendLine("select ' where ' + @keyname + ' = @' + @keyname  ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select 'if @@rowcount <= 0 ' union all   ");
        sb.AppendLine("select 'begin' union all  ");
        sb.AppendLine("select '    set @outmsg = ''更新失败''' union all  "); 
        sb.AppendLine("select '    return 0' union all   ");
        sb.AppendLine("select 'end' union all   ");
        sb.AppendLine("select '' union all  ");
        sb.AppendLine("select 'set @outmsg = ''更新成功''' union all   ");
        sb.AppendLine("select 'return 1' union all   ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select 'go'  ");
          
        sb.AppendLine("select code as proc_update from @tmptb  ");
          
          
        sb.AppendLine("-- proc_delete  ");
        sb.AppendLine("delete from @tmptb  ");
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_delete'') is not null' "); 
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_delete'  ");
        sb.AppendLine("insert into @tmptb select 'go'  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'  ");
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_delete,0,1'  ");
        sb.AppendLine("insert into @tmptb select '-- Author: vincent'  ");
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: '  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' "); 
        sb.AppendLine("insert into @tmptb select ''  ");
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_delete'  ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select top 1  ");
        sb.AppendLine("'@' + a.name + ' ' +   ");
        sb.AppendLine("case b.name   ");
        sb.AppendLine("when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''      ");
        sb.AppendLine("when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''      ");
        sb.AppendLine("when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''      ");
        sb.AppendLine("when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''''  ");
        sb.AppendLine("else b.name + ' = 0 ,' end  ");
        sb.AppendLine("from syscolumns a inner join systypes b  ");    
        sb.AppendLine("on a.xtype = b.xtype       ");
        sb.AppendLine("where a.id = object_id(@tablename)");      
        sb.AppendLine("order by a.colid   ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select '@outmsg varchar(256) = '''' output' union all  ");
        sb.AppendLine("select 'as' union all  ");
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all "); 
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all  ");
        sb.AppendLine("select '' "); 
          
        sb.AppendLine("insert into @tmptb select 'delete from ' + @tablename + ' where ' + @keyname + ' = @' + @keyname  ");
          
        sb.AppendLine("insert into @tmptb  ");
        sb.AppendLine("select '' union all   ");
        sb.AppendLine("select 'if @@rowcount <= 0 ' union all  ");
        sb.AppendLine("select 'begin' union all  ");
        sb.AppendLine("select '    set @outmsg = ''删除失败''' union all  "); 
        sb.AppendLine("select '    return 0' union all   ");
        sb.AppendLine("select 'end' union all   ");
        sb.AppendLine("select '' union all  ");
        sb.AppendLine("select 'set @outmsg = ''删除成功''' union all   ");
        sb.AppendLine("select 'return 1' union all   ");
        sb.AppendLine("select '' union all select 'go'  ");
          
        sb.AppendLine("select code as proc_delete from @tmptb "); 
          
        sb.AppendLine("-- proc_detail  ");
        sb.AppendLine("delete from @tmptb  ");
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_detail'') is not null'  ");
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_detail'  ");
        sb.AppendLine("insert into @tmptb select 'go'  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'  ");
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_detail,2,0'  ");
        sb.AppendLine("insert into @tmptb select '-- Author: vincent'  ");
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");
        sb.AppendLine("insert into @tmptb select '-- Description: '  ");
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'  ");
        sb.AppendLine("insert into @tmptb select ''  ");
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_detail'  ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select top 1  ");
        sb.AppendLine("'@' + a.name + ' ' +   ");
        sb.AppendLine("case b.name   ");
        sb.AppendLine("when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''      ");
        sb.AppendLine("when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''      ");
        sb.AppendLine("when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''  ");    
        sb.AppendLine("when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = ''''' "); 
        sb.AppendLine("else b.name + ' = 0' end  ");
        sb.AppendLine("from syscolumns a inner join systypes b ");     
        sb.AppendLine("on a.xtype = b.xtype       ");
        sb.AppendLine("where a.id = object_id(@tablename)    ");  
        sb.AppendLine("order by a.colid   ");
          
        sb.AppendLine("insert into @tmptb   ");
        sb.AppendLine("select 'as' union all  ");
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all  ");
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all  ");
        sb.AppendLine("select ''  ");
          
        sb.AppendLine("insert into @tmptb select 'select * from ' + @tablename + ' with(nolock) where ' + @keyname + ' = @' + @keyname  ");
          
        sb.AppendLine("insert into @tmptb select '' union all select 'go'  ");
          
        sb.AppendLine("select code as proc_detail from @tmptb  ");
          
        sb.AppendLine("-- proc_listall  ");
        sb.AppendLine("delete from @tmptb  ");
        sb.AppendLine("insert into @tmptb select 'if object_id(N''p_' + @proctablename + '_listall'') is not null' "); 
        sb.AppendLine("insert into @tmptb select ' drop procedure p_' + @proctablename + '_listall' ");  
        sb.AppendLine("insert into @tmptb select 'go'  ");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------' ");   
        sb.AppendLine("insert into @tmptb select '-- Procedure Name: cc p_' + @proctablename + '_listall,1,0' ");   
        sb.AppendLine("insert into @tmptb select '-- Author: vincent'");  
        sb.AppendLine("insert into @tmptb select '-- Date Generated: ' + datename(year,getdate()) + '年' + datename(month,getdate()) + '月'  + datename(day,getdate()) + '日'  ");  
        sb.AppendLine("insert into @tmptb select '-- Description: '");  
        sb.AppendLine("insert into @tmptb select '----------------------------------------------------------'");  
        sb.AppendLine("insert into @tmptb select ''");  
        sb.AppendLine("insert into @tmptb select 'create procedure p_' + @proctablename + '_listall' "); 
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select 'as' union all");  
        sb.AppendLine("select 'set nocount on -- 不返回计数，提高执行效率 ' union all");  
        sb.AppendLine("select 'set transaction isolation level read uncommitted -- 指定语句不能读取由其它事务修改但尚未提交的数据，避免脏读；其它事务可在当前事务的各语句之间更改数据，从面产生不可重复读取和纪像数据。' union all  ");
        sb.AppendLine("select 'set xact_abort on -- 语句产生运行时错误，整个事务将终止并回滚 ' union all ");  
        sb.AppendLine("select '' "); 
          
        sb.AppendLine("insert into @tmptb ");  
        sb.AppendLine("select 'select * from ' + @tablename + ' with(nolock) '");  
          
        sb.AppendLine("insert into @tmptb select '' union all select 'go'");            
        sb.AppendLine("select code as proc_listall from @tmptb");
        sb.AppendLine("GO\r");

        #endregion


        #region 根据表名生成数据视图类

        sb.AppendLine("/*");         
        sb.AppendLine("功能: 根据表名生成数据视图类");   
        sb.AppendLine("作者: vincent");                             
        sb.AppendLine("日期: " + DateTime.Now.ToString());                                                                 
        sb.AppendLine("*/");
        sb.AppendLine("if object_id(N'[dbo].[cv]') is not null");
        sb.AppendLine("drop procedure [dbo].[cv]");
        sb.AppendLine("go");
        sb.AppendLine("CREATE procedure [dbo].[cv]  "); 
        sb.AppendLine("@tablename varchar(50) = ''  ");    
        sb.AppendLine("as      "); 
        sb.AppendLine("set nocount on  "); 
        sb.AppendLine("set transaction isolation level read uncommitted  "); 
        sb.AppendLine("set xact_abort on  "); 
          
        sb.AppendLine("if object_id(@tablename) is null   ");     
        sb.AppendLine("begin      "); 
        sb.AppendLine("    select 'table not exists' as error  "); 
        sb.AppendLine("    return      "); 
        sb.AppendLine("end  "); 
          
        sb.AppendLine("declare @tmptb table(code varchar(2048))  "); 
        sb.AppendLine("declare @totalcols int  "); 
        sb.AppendLine("declare @classname varchar(50)  "); 
        sb.AppendLine("declare @word varchar(50)  "); 
        sb.AppendLine("declare @pos int  "); 
        sb.AppendLine("declare @func varchar(2048)  "); 
          
        sb.AppendLine("select @totalcols = count(1) from syscolumns where id = object_id(@tablename) ");   
          
          
        sb.AppendLine("set @classname = @tablename ");    
        sb.AppendLine("if( @classname <> '' and left(@classname,2) = 't_')  ");  
        sb.AppendLine("begin  ");  
        sb.AppendLine("    set @classname = substring(@classname,3,len(@classname) - 2)  ");  
        sb.AppendLine("end  ");  
          
        sb.AppendLine("set @classname = upper(left(@classname,1)) + substring(@classname,2,len(@classname) - 1) + 'Info' ");   
        sb.AppendLine("while( charindex('_',@classname) > 0 )  ");  
        sb.AppendLine("begin  ");  
        sb.AppendLine("    set @pos = charindex('_',@classname) ");   
        sb.AppendLine("    set @word = substring(@classname,@pos + 1, 1)  ");  
        sb.AppendLine("    set @classname = replace(@classname,'_' + @word,upper(@word) )  ");  
        sb.AppendLine("end  ");  
          
        sb.AppendLine("select @classname + '.cs' as 数据视图类文件名");    
          
        sb.AppendLine("set @func = 'public ' + @classname + '('  ");  
        sb.AppendLine("select @func = @func +  ");                            
        sb.AppendLine("case type_name(xusertype)   ");                           
        sb.AppendLine("when 'varchar' then  'string'  ");                            
        sb.AppendLine("when 'nvarchar' then 'string'   ");                           
        sb.AppendLine("when 'decimal' then 'decimal'  ");                            
        sb.AppendLine("when 'tinyint' then 'int' ");                         
        sb.AppendLine("when 'smallint' then 'int'     ");                         
        sb.AppendLine("when 'datetime' then 'DateTime'   ");                           
        sb.AppendLine("when 'smalldatetime' then 'DateTime' ");                             
        sb.AppendLine("when 'char' then 'string'  ");                       
        sb.AppendLine("when 'nchar' then 'string'   ");                           
        sb.AppendLine("when 'bigint'  then 'long' ");                             
        sb.AppendLine("when 'text' then 'string'  ");                            
        sb.AppendLine("when 'ntext' then 'string'  ");   
        sb.AppendLine("when 'numeric' then 'float' ");    
        sb.AppendLine("when 'bit' then 'bool' ");                   
        sb.AppendLine("else type_name(xusertype) ");                             
        sb.AppendLine("end ");                              
        sb.AppendLine(" +   ' '+  name +  ");
        sb.AppendLine("case when colid < @totalcols then ', ' else '' end  ");
        sb.AppendLine("from syscolumns  where id = object_id(@tablename) order by colid  ");
          
        sb.AppendLine("select @func = @func + ')'  ");
          
        sb.AppendLine("insert into @tmptb select @func  ");
        sb.AppendLine("insert into @tmptb select '{'   ");
        sb.AppendLine("insert into @tmptb select 'this._' + name + ' = ' + name + ';' from syscolumns  where id = object_id(@tablename) order by colid  ");
        sb.AppendLine("insert into @tmptb select '}'  ");
        sb.AppendLine("insert into @tmptb select ''  ");
        sb.AppendLine("insert into @tmptb select '#region 公共属性' "); 
                    
        sb.AppendLine("declare @parmType varchar(50)");  
        sb.AppendLine("declare @parmName varchar(50) "); 
        sb.AppendLine("declare @colid int  ");
        sb.AppendLine("declare @cur cursor   ");
        sb.AppendLine("set @cur = cursor read_only forward_only for ");  
        sb.AppendLine("select b.name,a.name,a.colid from syscolumns a inner join systypes b on a.xtype = b.xtype where a.id = object_id(@tablename)  ");
          
        sb.AppendLine("open @cur  "); 
        sb.AppendLine("fetch next from @cur into @parmType,@parmName,@colid "); 
        sb.AppendLine("while(@@fetch_status = 0)  ");
        sb.AppendLine("begin  ");
          
        sb.AppendLine("    insert into @tmptb   ");
        sb.AppendLine("    select  ");
        sb.AppendLine("    'private ' + case @parmType"); 
        sb.AppendLine("    when 'int' then 'int'     ");
        sb.AppendLine("    when 'bigint' then 'long'  ");
        sb.AppendLine("    when 'tinyint' then 'byte' "); 
        sb.AppendLine("    when 'smallint' then 'short' ");
        sb.AppendLine("    when 'float' then 'float'  ");
        sb.AppendLine("    when 'double' then 'double'  ");
        sb.AppendLine("    when 'char' then 'string'  ");    
        sb.AppendLine("    when 'nchar' then 'string' ");     
        sb.AppendLine("    when 'varchar' then 'string'   ");   
        sb.AppendLine("    when 'nvarchar' then 'string'  "); 
        sb.AppendLine("    when 'text' then 'string'  ");                           
	    sb.AppendLine("    when 'ntext' then 'string'  ");
        sb.AppendLine("    when 'xml' then 'string'  ");
        sb.AppendLine("    when 'smalldatetime' then 'DateTime'  ");
        sb.AppendLine("    when 'datetime' then 'DateTime'    ");
        sb.AppendLine("    when 'bit' then 'bool'    ");
	    sb.AppendLine("    when 'numeric' then 'decimal'  ");
        sb.AppendLine("    when 'decimal' then 'decimal'  ");
        sb.AppendLine("    when 'money' then 'decimal'  ");
        sb.AppendLine("    when 'smallmoney' then 'decimal'  ");

        sb.AppendLine("    else @parmType end + ' _' + @parmName + ';'  ");   
              
        sb.AppendLine("    -- sysproperties");
        sb.AppendLine("    if exists(select 1 from sys.extended_properties where major_id = object_id(@tablename) and minor_id = @colid) "); 
        sb.AppendLine("        insert into @tmptb   ");
        sb.AppendLine("        select '/// <summary>' union all  ");
        sb.AppendLine("        select '/// ' + convert(varchar,value) from sys.extended_properties where major_id = object_id(@tablename) and minor_id = @colid  union all  ");
        sb.AppendLine("        select '/// </summary>'   ");
        sb.AppendLine("    else  ");
        sb.AppendLine("        insert into @tmptb   ");
        sb.AppendLine("        select '/// <summary>' union all  ");
        sb.AppendLine("        select '/// ' union all   ");
        sb.AppendLine("        select '/// </summary>'   ");
          
        sb.AppendLine("    insert into @tmptb   ");
        sb.AppendLine("    select       ");
        sb.AppendLine("    'public ' + case @parmType  ");
        sb.AppendLine("    when 'decimal' then 'decimal'   ");    
        sb.AppendLine("    when 'int' then 'int'      ");
        sb.AppendLine("    when 'smallint' then 'int'    ");  
        sb.AppendLine("    when 'tinyint' then 'int'     "); 
        sb.AppendLine("    when 'bigint' then 'long'    ");  
        sb.AppendLine("    when 'bit' then 'bool'      ");
        sb.AppendLine("    when 'char' then 'string'     "); 
        sb.AppendLine("    when 'nchar' then 'string'    ");  
        sb.AppendLine("    when 'varchar' then 'string'     "); 
        sb.AppendLine("    when 'nvarchar' then 'string'     "); 
        sb.AppendLine("    when 'smalldatetime' then 'DateTime'  ");    
        sb.AppendLine("    when 'datetime' then 'DateTime'  ");
        sb.AppendLine("    when 'text' then 'string'          ");                  
	    sb.AppendLine("    when 'ntext' then 'string'   ");
	    sb.AppendLine("    when 'numeric' then 'float'       ");
        sb.AppendLine("    else @parmType end       ");
        sb.AppendLine("    + ' ' + upper(left(@parmName,1)) + right(@parmName,len(@parmName) - 1)   ");    
        sb.AppendLine("    + '    {'      ");
        sb.AppendLine("    + '    get { return _' + @parmName + '; }'      ");
        sb.AppendLine("    + '    set { _' + @parmName + ' = value ; }'      ");
        sb.AppendLine("    + '    }'         ");
          
        sb.AppendLine("    insert into @tmptb select ''  ");
          
        sb.AppendLine("    fetch next from @cur into @parmType,@parmName,@colid  ");
          
        sb.AppendLine("end  ");
        sb.AppendLine("insert into @tmptb select '#endregion'  ");

        sb.AppendLine("select code as 类字段和属性 from @tmptb ");
        sb.AppendLine("GO\r");

        #endregion


        return sb.ToString();
    }

    /// <summary>
    /// 生成常用sql,全部保存在一个文件中
    /// </summary>
    /// <param name="dt"> 所有表名 </param>
    /// <param name="db_name"> 数据库名 </param>
    /// <returns></returns>
    public static string CreateSql(DataTable dt, string db_name, CDatabase db)
    {
        // 新建目录        
        string directoryPath = CUtility.GetBaseDirectory() + db_name + "\\";
        bool exists = CFile.CreateDirectory(directoryPath);

        // 添加文件
        string filePath = CUtility.GetBaseDirectory() + db_name + "\\" + db_name + ".sql";

        if (exists)
        {
            // 写文件内容
            StreamWriter w = new StreamWriter(filePath, true, Encoding.Default);

            /*********************** 写入固定sql *********************/
            w.WriteLine("use " + db_name);
            w.WriteLine("go\r");

            w.WriteLine(GetAutoCreateCodeSql());

            w.WriteLine("if object_id(N'P_SplitPageOneSql') is not null");
            w.WriteLine("drop procedure P_SplitPageOneSql");
            w.WriteLine("go");
            w.WriteLine("-- vicnent 分页");
            w.WriteLine("create procedure [dbo].[P_SplitPageOneSql]");
            w.WriteLine("@tablename varchar(32),");
            w.WriteLine("@pagesize int,             --页面大小 ");
            w.WriteLine("@pagepos int,              --第几页 ");
            w.WriteLine("@orderby  varchar(64),     --排序顺序,多个则用", "分隔 加上前缀b.");
            w.WriteLine("@orderby2 varchar(64),     --排序顺序的反向排序,多个则用", "分隔 加上前缀b.");
            w.WriteLine("@orderbyclause varchar(64),--排序字段(不包括desc ,asc等),以", "分隔  加上前缀b.");
            w.WriteLine("@keyname varchar(32),      --主键    ");
            w.WriteLine("@keyinorder tinyint ,       --主键是否在排序字段中 1:在  0:不在 ");
            w.WriteLine("@whereclause varchar(256) , --where条件 ");
            w.WriteLine("@showclause varchar(256),  --显示的字段 加上前缀a.  --注意如果显示字段过长的话,需要再加长 ");
            w.WriteLine("@totalcount int output  --是否需要统计总数 -1:要统计 其它不统计  ");
            w.WriteLine("as\r");
            w.WriteLine("set nocount on");
            w.WriteLine("set transaction isolation level read uncommitted ");
            w.WriteLine("declare @sql nvarchar(1024) --注意如果显示字段过长的话,需要再加长");
            w.WriteLine("declare @showrows smallint  --显示的行数(可能在最后一页,这样显示的记录数就要小于@pagesize ");
            w.WriteLine("declare @top9clause nvarchar(128) ");
            w.WriteLine("if @keyinorder = 0 ");
            w.WriteLine("    set @top9clause = @orderbyclause + ',b.' + @keyname ");
            w.WriteLine("else  ");
            w.WriteLine("    set @top9clause = @orderbyclause ");
            w.WriteLine("if @whereclause is null  ");
            w.WriteLine("    set @whereclause = '' ");
            w.WriteLine("if @totalcount = 0   ");
            w.WriteLine("begin");
            w.WriteLine("    set @sql = 'set @totalcount = (select count(1) from ' + @tablename + ' ' + @whereclause +')' ");
            w.WriteLine("    exec sp_executesql @sql,N'@totalcount int output',@totalcount output  ");
            w.WriteLine("end\r");
            w.WriteLine("--要处理好翻到最后一页且不能满页时的情况 ");
            w.WriteLine("if @totalcount > @pagesize * (@pagepos -1) and @totalcount < @pagesize * @pagepos ");
            w.WriteLine("   set @showrows = @totalcount - @pagesize * (@pagepos -1)");
            w.WriteLine("else if  @totalcount >= @pagesize * @pagepos ");
            w.WriteLine("   set @showrows = @pagesize ");
            w.WriteLine("else  ");
            w.WriteLine("   return\r");
            w.WriteLine("set @sql = 'select '   +   @showclause +' from (  ");
            w.WriteLine("select top  ' + convert(varchar, @showrows)  + '  ' + @top9clause +  ' from( ");
            w.WriteLine("select top ' + convert(varchar,@pagesize * @pagepos) + ' '");
            w.WriteLine(" + @top9clause +  '  from ");
            w.WriteLine("  ' + @tablename  + '  b ' +  @whereclause  +' order by ' + @orderby + ') b  ");
            w.WriteLine("order by  '  + @orderby2 + ') b inner join ' + @tablename  + '  a on a.' + @keyname +' = b.' + @keyname +' ");
            w.WriteLine("order by ' + @orderby  \r");
            w.WriteLine("print @sql");
            w.WriteLine("exec sp_executesql @sql");
            w.WriteLine("\r\n");

            // 根据表名查询出表结构  
            string tablename = "";      // 表名
            string keyName = "";        // 主键名            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tablename = dt.Rows[i]["TABLE_NAME"].ToString();

                if (tablename.Equals(""))
                    continue;

                keyName = CUtility.GetKeyNameByTableName(tablename, db);

                // 生成 p_tablename_list 脚本
                w.WriteLine("go");
                w.WriteLine("\r");
                w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_list') is not null");
                w.WriteLine("drop procedure p_" + tablename.Replace("t_", "") + "_list");
                w.WriteLine("go");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("-- Author: vincent");
                w.WriteLine("-- Date Generated: " + DateTime.Now.ToString());
                w.WriteLine("-- Description: 分页查询");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_list");
                w.WriteLine("@page int = 1, --当前页");
                w.WriteLine("@pagesize int = 10, --每页显示");
                w.WriteLine("@total int = 0 output, --总记录数");
                w.WriteLine("@condition varchar(4000) = '' -- 查询条件 \r");
                w.WriteLine("as");
                w.WriteLine("set nocount on");
                w.WriteLine("set transaction isolation level read uncommitted");
                w.WriteLine("set xact_abort on");
                w.WriteLine("declare @tablename varchar(1024)");
                w.WriteLine("declare @orderby  varchar(1024)");
                w.WriteLine("declare @orderby2 varchar(1024)");
                w.WriteLine("declare @orderbyclause varchar(1024)");
                w.WriteLine("declare @keyname varchar(50)");
                w.WriteLine("declare @keyinorder tinyint");
                w.WriteLine("declare @whereclause varchar(1024)");
                w.WriteLine("declare @showclause varchar(1024)");
                w.WriteLine("-- 表名称");
                w.WriteLine("set @tablename = '" + tablename + "' ");
                w.WriteLine("--排序字段,字段前面加b.,字段后加 desc或asc,格式如 b.rid desc,b.rname asc");
                w.WriteLine("set @orderby = 'b." + keyName + " desc' ");
                w.WriteLine("--排序字段,字段前面加b.,字段后面和上面的orderby正好相反,格式如 b.rid asc,b.ranme desc");
                w.WriteLine("set @orderby2 = 'b." + keyName + " asc' ");
                w.WriteLine("--排序字段，这里不需要加 asc 和 desc，格式如 b.rid,b.rname");
                w.WriteLine("set @orderbyclause = 'b." + keyName + "'");
                w.WriteLine("--主键是否在排序字段里，1 是 0 不是，一般是1");
                w.WriteLine("set @keyinorder = 1");
                w.WriteLine("--这个表的主键");
                w.WriteLine("set @keyname = '" + keyName + "'");
                w.WriteLine("--查询条件");
                w.WriteLine("set @whereclause = @condition");
                w.WriteLine("set @showclause = 'a.*'");
                w.WriteLine("--调用分页存储过程");
                w.WriteLine("exec P_SplitPageOneSql @tablename,@pagesize,@page,@orderby,@orderby2,");
                w.WriteLine("@orderbyclause,@keyname,@keyinorder,@whereclause,@showclause,@total output");
                w.WriteLine("go");
                w.WriteLine("\r");

                // 生成 p_tablename_save 脚本
                w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_save') is not null");
                w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_save");
                w.WriteLine("go");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("-- Author: vincent");
                w.WriteLine("-- Date Generated: " + DateTime.Now.ToString());
                w.WriteLine("-- Description: 新增｜修改");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_save");

                string sql = "sp_COLUMNS '" + tablename + "'";
                DataTable dt_columns = db.ExecuteSql(sql);

                string column_name = "";        // 字段名称
                string column_typename = "";    // varchar,int,datetime,float...
                string column_length = "";      // 长度
                string fieldsname = "";         // 如: name,typeid,remark
                string parmname = "";           // 如: @name,@typeid,@remark
                string fieldsname_update = "";  // 如: typeid = @typeid, remark=@remark

                for (int j = 0; j < dt_columns.Rows.Count; j++)
                {
                    column_name = dt_columns.Rows[j]["column_name"].ToString();
                    column_typename = dt_columns.Rows[j]["type_name"].ToString();
                    column_length = dt_columns.Rows[j]["length"].ToString();

                    if (column_typename.Contains("int"))
                        w.WriteLine("@" + column_name + " int = 0,");
                    else if (column_typename.Contains("varchar"))
                        w.WriteLine("@" + column_name + " varchar(" + column_length + ") = '',");
                    else if (column_typename.Contains("nvarchar"))
                        w.WriteLine("@" + column_name + " nvarchar(" + column_length + ") = '',");
                    else if (column_typename.Contains("text"))
                        w.WriteLine("@" + column_name + " text", "");
                    else if (column_typename.Contains("float"))
                        w.WriteLine("@" + column_name + " float = 0,");
                    else if (column_typename.Contains("double"))
                        w.WriteLine("@" + column_name + " double = 0,");
                    else if (column_typename.ToLower().Contains("datetime"))
                        w.WriteLine("@" + column_name + " DateTime,");
                    else if (column_typename.Contains("bigint"))
                        w.WriteLine("@" + column_name + " bigint = 0,");
                    else if (column_typename.Contains("smallint"))
                        w.WriteLine("@" + column_name + " smallint = 0,");
                    else if (column_typename.Contains("bit"))
                        w.WriteLine("@" + column_name + " bit = 0,");
                    else if (column_typename.Contains("money"))
                        w.WriteLine("@" + column_name + " money = 0,");
                    else if (column_typename.Contains("smallmoney"))
                        w.WriteLine("@" + column_name + " smallmoney = 0,");
                    else if (column_typename.Contains("decimal"))
                        w.WriteLine("@" + column_name + " decimal = 0,");
                    else if (column_typename.Contains("numeric"))
                        w.WriteLine("@" + column_name + " numeric = 0,");
                    else if (column_typename.Contains("nchar"))
                        w.WriteLine("@" + column_name + " nchar(" + column_length + ") = '',");
                    else if (column_typename.Contains("char"))
                        w.WriteLine("@" + column_name + " char = '',");
                    
                    if (column_name != keyName)
                    {
                        fieldsname += column_name;
                        parmname += "@" + column_name;
                        fieldsname_update += column_name + "=@" + column_name;

                        if (j < dt_columns.Rows.Count - 1)
                        {
                            fieldsname += ",";
                            parmname += ",";
                            fieldsname_update += ",";
                        }
                    }
                }
                w.WriteLine("@outmsg varchar(256) = '' output,");
                w.WriteLine("@newid int = 0 output");
                w.WriteLine("as");
                w.WriteLine("set nocount on");
                w.WriteLine("set transaction isolation level read uncommitted");
                w.WriteLine("set xact_abort on\r");
                w.WriteLine("if @" + keyName + " <= 0 ");
                w.WriteLine("begin");
                w.WriteLine("insert into " + tablename + "(" + fieldsname + ")");
                w.WriteLine("values(" + parmname + ")\r");
                w.WriteLine("  if @@rowcount <= 0 ");
                w.WriteLine("  begin");
                w.WriteLine("     set @outmsg = '添加失败'");
                w.WriteLine("     return 0");
                w.WriteLine("  end");
                w.WriteLine("  set @newid = @@identity\r");
                w.WriteLine("  if @newid <= 0");
                w.WriteLine("  begin");
                w.WriteLine("     set @outmsg = '添加失败'");
                w.WriteLine("     return 0");
                w.WriteLine("  end");
                w.WriteLine("  set @outmsg = '添加成功'");
                w.WriteLine("  return 1");
                w.WriteLine("end");
                w.WriteLine("else");
                w.WriteLine("begin");
                w.WriteLine("update " + tablename + " set " + fieldsname_update + " where " + keyName + " = @" + keyName + "");
                w.WriteLine("if @@rowcount <= 0 ");
                w.WriteLine("begin");
                w.WriteLine("   set @outmsg = '更新失败'");
                w.WriteLine("   return 0");
                w.WriteLine("end\r");
                w.WriteLine("set @newid = @" + keyName + "");
                w.WriteLine("set @outmsg = '更新成功'");
                w.WriteLine("return 1");
                w.WriteLine("end");
                w.WriteLine("go");
                w.WriteLine("\r");

                // 生成 p_tablename_detail 脚本
                w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_detail') is not null");
                w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_detail");
                w.WriteLine("go");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("-- Author: vincent");
                w.WriteLine("-- Date Generated: " + DateTime.Now.ToString());
                w.WriteLine("-- Description: 详细信息");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_detail");
                w.WriteLine("@" + keyName + " int = 0");
                w.WriteLine("as");
                w.WriteLine("set nocount on");
                w.WriteLine("set transaction isolation level read uncommitted");
                w.WriteLine("set xact_abort on\r");
                w.WriteLine("select * from " + tablename + " where " + keyName + " = @" + keyName + "\r");
                w.WriteLine("go");

                // 生成 p_tablename_delete 脚本
                w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_delete') is not null");
                w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_delete");
                w.WriteLine("go");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("-- Author: vincent");
                w.WriteLine("-- Date Generated: " + DateTime.Now.ToString());
                w.WriteLine("-- Description: 删除");
                w.WriteLine("----------------------------------------------------------");
                w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_delete");
                w.WriteLine("@" + keyName + " int = 0,");
                w.WriteLine("@outmsg varchar(256) = '' output");
                w.WriteLine("as");
                w.WriteLine("set nocount on");
                w.WriteLine("set transaction isolation level read uncommitted");
                w.WriteLine("set xact_abort on\r");
                w.WriteLine("delete from " + tablename + " where " + keyName + " = @" + keyName + "\r");
                w.WriteLine("if @@rowcount <= 0 ");
                w.WriteLine("begin");
                w.WriteLine("    set @outmsg = '删除失败'");
                w.WriteLine("    return 0");
                w.WriteLine("end\r");
                w.WriteLine("set @outmsg = '删除成功'");
                w.WriteLine("return 1");
                w.WriteLine("go");
                w.WriteLine("\r");
            }

            w.Flush();
            w.Close();
        }

        return db_name + ".sql 生成成功";
    }

    /// <summary>
    /// 生成常用sql,保存为不同文件
    /// </summary>
    /// <returns></returns>
    public static string CreateSql(string db_name, string tablename, CDatabase db)
    {
        // 新建目录
        string fileName = CUtility.GetTableName(tablename);
        string directoryPath = CUtility.GetBaseDirectory() + db_name + "\\Sql\\";
        bool exists = CFile.CreateDirectory(directoryPath);

        // 添加文件
        string filePath = CUtility.GetBaseDirectory() + db_name + "\\Sql\\" + fileName + ".sql";
        if (exists)
        {
            string sql = "sp_COLUMNS '" + tablename + "'";
            DataTable dt_columns = db.ExecuteSql(sql);

            // 主键名称 
            string keyName = CUtility.GetKeyNameByTableName(tablename, db);

            StreamWriter w = new StreamWriter(filePath, true, Encoding.Default);
            w.WriteLine("use " + db_name);
            w.WriteLine("go\r");
            

            // 生成 p_tablename_list 脚本            
            w.WriteLine("\r");
            w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_list') is not null");
            w.WriteLine("drop procedure p_" + tablename.Replace("t_", "") + "_list");
            w.WriteLine("go");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("-- Author: vincent");
            w.WriteLine("-- Date Generated: " + DateTime.Now.ToString());
            w.WriteLine("-- Description: 分页查询");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_list");
            w.WriteLine("@page int = 1, --当前页");
            w.WriteLine("@pagesize int = 10, --每页显示");
            w.WriteLine("@total int = 0 output, --总记录数");
            w.WriteLine("@condition varchar(4000) = '' -- 查询条件 \r");
            w.WriteLine("as");
            w.WriteLine("set nocount on");
            w.WriteLine("set transaction isolation level read uncommitted");
            w.WriteLine("set xact_abort on");
            w.WriteLine("declare @tablename varchar(1024)");
            w.WriteLine("declare @orderby  varchar(1024)");
            w.WriteLine("declare @orderby2 varchar(1024)");
            w.WriteLine("declare @orderbyclause varchar(1024)");
            w.WriteLine("declare @keyname varchar(50)");
            w.WriteLine("declare @keyinorder tinyint");
            w.WriteLine("declare @whereclause varchar(1024)");
            w.WriteLine("declare @showclause varchar(1024)");
            w.WriteLine("-- 表名称");
            w.WriteLine("set @tablename = '" + tablename + "' ");
            w.WriteLine("--排序字段,字段前面加b.,字段后加 desc或asc,格式如 b.rid desc,b.rname asc");
            w.WriteLine("set @orderby = 'b." + keyName + " desc' ");
            w.WriteLine("--排序字段,字段前面加b.,字段后面和上面的orderby正好相反,格式如 b.rid asc,b.ranme desc");
            w.WriteLine("set @orderby2 = 'b." + keyName + " asc' ");
            w.WriteLine("--排序字段，这里不需要加 asc 和 desc，格式如 b.rid,b.rname");
            w.WriteLine("set @orderbyclause = 'b." + keyName + "'");
            w.WriteLine("--主键是否在排序字段里，1 是 0 不是，一般是1");
            w.WriteLine("set @keyinorder = 1");
            w.WriteLine("--这个表的主键");
            w.WriteLine("set @keyname = '" + keyName + "'");
            w.WriteLine("--查询条件");
            w.WriteLine("set @whereclause = @condition");
            w.WriteLine("set @showclause = 'a.*'");
            w.WriteLine("--调用分页存储过程");
            w.WriteLine("exec P_SplitPageOneSql @tablename,@pagesize,@page,@orderby,@orderby2,");
            w.WriteLine("@orderbyclause,@keyname,@keyinorder,@whereclause,@showclause,@total output");
            w.WriteLine("go");
            w.WriteLine("\r");

            // 生成 p_tablename_save 脚本
            w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_save') is not null");
            w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_save");
            w.WriteLine("go");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("-- Author: vincent");
            w.WriteLine("-- Date Generated: " + DateTime.Now.ToShortDateString());
            w.WriteLine("-- Description: 新增｜修改");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_save");

            string column_name = "";        // 字段名称
            string column_typename = "";    // varchar,int,datetime,float...
            string column_length = "";      // 长度
            string fieldsname = "";         // 如: name,typeid,remark
            string parmname = "";           // 如: @name,@typeid,@remark
            string fieldsname_update = "";  // 如: typeid = @typeid, remark=@remark

            for (int j = 0; j < dt_columns.Rows.Count; j++)
            {
                column_name = dt_columns.Rows[j]["column_name"].ToString();
                column_typename = dt_columns.Rows[j]["type_name"].ToString();
                column_length = dt_columns.Rows[j]["length"].ToString();

                if (column_typename.Contains("int"))
                    w.WriteLine("@" + column_name + " int = 0,");
                else if (column_typename.Contains("varchar"))
                    w.WriteLine("@" + column_name + " varchar(" + column_length + ") = '',");
                else if (column_typename.Contains("nvarchar"))
                    w.WriteLine("@" + column_name + " nvarchar(" + column_length + ") = '',");
                else if (column_typename.Contains("text"))
                    w.WriteLine("@" + column_name + " text", "");
                else if (column_typename.Contains("float"))
                    w.WriteLine("@" + column_name + " float = 0,");
                else if (column_typename.Contains("double"))
                    w.WriteLine("@" + column_name + " double = 0,");
                else if (column_typename.ToLower().Contains("datetime"))
                    w.WriteLine("@" + column_name + " DateTime,");
                else if (column_typename.Contains("bigint"))
                    w.WriteLine("@" + column_name + " bigint = 0,");
                else if (column_typename.Contains("smallint"))
                    w.WriteLine("@" + column_name + " smallint = 0,");
                else if (column_typename.Contains("bit"))
                    w.WriteLine("@" + column_name + " bit = 0,");
                else if (column_typename.Contains("money"))
                    w.WriteLine("@" + column_name + " money = 0,");
                else if (column_typename.Contains("smallmoney"))
                    w.WriteLine("@" + column_name + " smallmoney = 0,");
                else if (column_typename.Contains("decimal"))
                    w.WriteLine("@" + column_name + " decimal = 0,");
                else if (column_typename.Contains("numeric"))
                    w.WriteLine("@" + column_name + " numeric = 0,");
                else if (column_typename.Contains("nchar"))
                    w.WriteLine("@" + column_name + " nchar(" + column_length + ") = '',");
                else if (column_typename.Contains("char"))
                    w.WriteLine("@" + column_name + " char = '',");

                if (column_name != keyName)
                {
                    fieldsname += column_name;
                    parmname += "@" + column_name;
                    fieldsname_update += column_name + "=@" + column_name;

                    if (j < dt_columns.Rows.Count - 1)
                    {
                        fieldsname += ",";
                        parmname += ",";
                        fieldsname_update += ",";
                    }
                }
            }
            w.WriteLine("@outmsg varchar(256) = '' output,");
            w.WriteLine("@newid int = 0 output");
            w.WriteLine("as");
            w.WriteLine("set nocount on");
            w.WriteLine("set transaction isolation level read uncommitted");
            w.WriteLine("set xact_abort on\r");
            w.WriteLine("if @" + keyName + " <= 0 ");
            w.WriteLine("begin");
            w.WriteLine("insert into " + tablename + "(" + fieldsname + ")");
            w.WriteLine("values(" + parmname + ")\r");
            w.WriteLine("  if @@rowcount <= 0 ");
            w.WriteLine("  begin");
            w.WriteLine("     set @outmsg = '添加失败'");
            w.WriteLine("     return 0");
            w.WriteLine("  end");
            w.WriteLine("  set @newid = @@identity\r");
            w.WriteLine("  if @newid <= 0");
            w.WriteLine("  begin");
            w.WriteLine("     set @outmsg = '添加失败'");
            w.WriteLine("     return 0");
            w.WriteLine("  end");
            w.WriteLine("  set @outmsg = '添加成功'");
            w.WriteLine("  return 1");
            w.WriteLine("end");
            w.WriteLine("else");
            w.WriteLine("begin");
            w.WriteLine("update " + tablename + " set " + fieldsname_update + " where " + keyName + " = @" + keyName + "");
            w.WriteLine("if @@rowcount <= 0 ");
            w.WriteLine("begin");
            w.WriteLine("   set @outmsg = '更新失败'");
            w.WriteLine("   return 0");
            w.WriteLine("end\r");
            w.WriteLine("set @newid = @" + keyName + "");
            w.WriteLine("set @outmsg = '更新成功'");
            w.WriteLine("return 1");
            w.WriteLine("end");
            w.WriteLine("go");
            w.WriteLine("\r");

            // 生成 p_tablename_detail 脚本
            w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_detail') is not null");
            w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_detail");
            w.WriteLine("go");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("-- Author: vincent");
            w.WriteLine("-- Date Generated: " + DateTime.Now.ToShortDateString());
            w.WriteLine("-- Description: 详细信息");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_detail");
            w.WriteLine("@" + keyName + " int = 0");
            w.WriteLine("as");
            w.WriteLine("set nocount on");
            w.WriteLine("set transaction isolation level read uncommitted");
            w.WriteLine("set xact_abort on\r");
            w.WriteLine("select * from " + tablename + " where " + keyName + " = @" + keyName + "\r");
            w.WriteLine("go");

            // 生成 p_tablename_delete 脚本
            w.WriteLine("if object_id(N'p_" + tablename.Replace("t_", "") + "_delete') is not null");
            w.WriteLine(" drop procedure p_" + tablename.Replace("t_", "") + "_delete");
            w.WriteLine("go");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("-- Author: vincent");
            w.WriteLine("-- Date Generated: " + DateTime.Now.ToShortDateString());
            w.WriteLine("-- Description: 删除");
            w.WriteLine("----------------------------------------------------------");
            w.WriteLine("create procedure p_" + tablename.Replace("t_", "") + "_delete");
            w.WriteLine("@" + keyName + " int = 0,");
            w.WriteLine("@outmsg varchar(256) = '' output");
            w.WriteLine("as");
            w.WriteLine("set nocount on");
            w.WriteLine("set transaction isolation level read uncommitted");
            w.WriteLine("set xact_abort on\r");
            w.WriteLine("delete from " + tablename + " where " + keyName + " = @" + keyName + "\r");
            w.WriteLine("if @@rowcount <= 0 ");
            w.WriteLine("begin");
            w.WriteLine("    set @outmsg = '删除失败'");
            w.WriteLine("    return 0");
            w.WriteLine("end\r");
            w.WriteLine("set @outmsg = '删除成功'");
            w.WriteLine("return 1");
            w.WriteLine("go");

            w.Flush();
            w.Close();
        }

        return tablename + ".sql生成成功";
    }

   
}

